USE master
GO
DROP DATABASE LogisticsManagement
/********************************************************************************
DB sử dụng tiền tố LG = Logistics cho tên các bảng trong LogisticsManagement	*
mục đích là để tránh trùng với các từ khóa trong Sql Server						*
********************************************************************************/
CREATE DATABASE LogisticsManagement
GO
USE LogisticsManagement
GO
/*
Nhân viên: Những nhân lực để vận hành
và quản lý hệ thống Logistics
*/
CREATE TABLE LG_Employees(--Nhân viên
	Id			INT PRIMARY KEY IDENTITY(1,1),
	Branch_Id	INT NOT NULL,
	Username	VARCHAR(30) UNIQUE NOT NULL,
	Password	VARCHAR(127) NOT NULL,

	First_Name	NVARCHAR(20) NOT NULL,-- Tên
	Last_Name	NVARCHAR(20) NOT NULL,-- Họ

	Sex			BIT NULL, --Giới tính
	Birth_Date	DATETIME NULL,-- Ngày/Tháng/Năm sinh
	Hire_Date	DATETIME NULL,-- Ngày đi làm
	Address		NVARCHAR(60) NULL, -- Địa chỉ
	City		NVARCHAR(15) NULL, -- Thành Phố
	Post_Code	NVARCHAR(24) NULL, -- Mã bưu chính
	Country		NVARCHAR(15) NULL, -- Quốc gia
	Phone		NVARCHAR (24) NULL, -- Điện thoại
	Email		NVARCHAR(50) NULL, -- Email
	Photo		NVARCHAR(255) NULL, -- Ảnh đại diện
	Notes		NTEXT NULL, -- Lưu chú
)

/*
Chức vụ: Các vai trò trong hệ thống	/ Admin/ Godown Clerk/Delivery Clerk v.v.để vận hành và quản lý hệ thống Logistic*/
CREATE TABLE LG_Roles(-- 
	Id			INT PRIMARY KEY IDENTITY(1,1),
	Title		NVARCHAR(30) NOT NULL UNIQUE,
	Description NVARCHAR(300) NULL
)

/*
Bảng trung gian, tác dụng để bổ nhiệm (Role) cho Employee. 1 Emp có thể có nhiều Role.
*/
CREATE TABLE LG_Employee_Role(
	Emp_Role_Id		INT PRIMARY KEY IDENTITY(1,1),
	Emp_Id			INT NOT NULL,
	Role_Id			INT NOT NULL,
	Appoint_Date	DATETIME NULL DEFAULT(GETDATE())
	UNIQUE(Emp_Id, Role_Id)
)

/*
Quyền: Bảng chứa các bản ghi về quyền cụ thể trong hệ thống Ege: Can Get Employee, Can Remove Employee v.v.v
*/

CREATE TABLE LG_Permissions(
	Id				INT PRIMARY KEY IDENTITY(1,1),
	Per_Code		NVARCHAR(127) NOT NULL UNIQUE,--Mã quyền
	Title			NVARCHAR(127) NOT NULL UNIQUE,
	Description		NVARCHAR(300) NULL
)

/*
Bảng trung gian, cho biết 1 role gồm những quyền (permiss) nào.
Ege: Admin ->
			. Can Add Employee
			. Can Remove Employee
			. Can View Stats
*/

CREATE TABLE LG_Role_Permissions(
	Role_Per_Id INT PRIMARY KEY IDENTITY(1,1),
	Role_Id		INT NOT NULL,
	Per_Id		INT NOT NULL,
	UNIQUE(Role_Id, Per_Id)
)

/*
Bảng ghi lại các Customer
*/

CREATE TABLE LG_Customers(
	Id				INT PRIMARY KEY IDENTITY(1,1),
	Identity_Card	NVARCHAR(15) NOT NULL UNIQUE,

	Contact_Name	NVARCHAR(30) NULL,
	Company_Name	NVARCHAR(40) NULL,
	Address			NVARCHAR(60) NULL,
	Post_Code		NVARCHAR(10) NULL,
	City			NVARCHAR(15) NULL,
	Country			NVARCHAR(15) NULL,
	Phone			NVARCHAR(24) NULL,
	Fax				NVARCHAR(24) NULL
)

CREATE TABLE LG_Cargoes
(
	Id				INT PRIMARY KEY IDENTITY(1,1),
	Weight			INT NOT NULL,
	Order_Id		INT NOT NULL,
	Date_Recieve	DATETIME NOT NULL DEFAULT(GETDATE())
)

/*Xe*/
CREATE TABLE LG_Vehicles
(
	Id				INT PRIMARY KEY IDENTITY(1,1),
	Vehicle_Name	NVARCHAR(127) NOT NULL,
	Tonnage			INT NOT NULL,--Tải trọng/kg
	Free_Vehicles	INT NULL
)

CREATE TABLE LG_Vehicle_Branch
(
	LG_Vehicle_Branch_Id	INT PRIMARY KEY IDENTITY(1,1),
	Branch_Id				INT NOT NULL,
	Vehicle_Id				INT NOT NULL,
	Quantity				INT NOT NULL,
	Free_Vehicle			INT NOT NULL,
	UNIQUE(Branch_Id, Vehicle_Id)
)

/*Địa điểm*/
CREATE TABLE LG_Locations(
	Id				INT PRIMARY KEY IDENTITY(1,1),
	Location_Name	NVARCHAR(127) NOT NULL UNIQUE
)

/*Cuộc vận chuyển*/
CREATE TABLE LG_Transportations
(
	Id				INT PRIMARY KEY IDENTITY(1,1),
	Branch_Id		INT NOT NULL,
	Route_Id		INT NOT NULL,
	Transp_Time		DATETIME NOT NULL DEFAULT(GETDATE()),
	Transp_Status	BIT	NOT NULL DEFAULT(0)
)


CREATE TABLE LG_Transportation_Carego
(
	Id			INT PRIMARY KEY IDENTITY(1,1),
	Transp_Id	INT NOT NULL,
	Carego_Id	INT NOT NULL,
	UNIQUE(Transp_Id, Carego_Id)
)

CREATE TABLE LG_Transportation_Vehicle
(
	Id			INT PRIMARY KEY IDENTITY(1,1),
	Transp_Id	INT NOT NULL,
	Vehicle_Id	INT NOT NULL,
	UNIQUE(Transp_Id, Vehicle_Id)
)

/*Tuyến đường*/
CREATE TABLE LG_Trips(
	Id				INT PRIMARY KEY IDENTITY(1,1),
	Trip_Name		NVARCHAR(255) NOT NULL,
	Km				INT NOT NULL
)

/*Lịch trình*/
CREATE TABLE LG_Routes(
	Id				INT PRIMARY KEY IDENTITY(1,1),
	DateStart		DATETIME NOT NULL DEFAULT(GETDATE()),
)

CREATE TABLE LG_Route_Trip(
	Id				INT PRIMARY KEY IDENTITY(1,1),
	Route_Id		INT NOT NULL,
	Trip_Id			INT NOT NULL,
	Order_Number	INT NOT NULL,
	UNIQUE(Route_Id, Trip_Id)
)

/*Đơn đặt hàng*/
CREATE TABLE LG_Orders(
	Id					INT PRIMARY KEY IDENTITY(1,1),
	Customer_Id			INT NOT NULL,
	Send_Location_Id	INT NOT NULL,--Địa điểm nhận hàng,References to Location_Id in the Locations table
	Consignee_Location_Id	INT NOT NULL,
	Send_Address		NVARCHAR(127) NOT NULL,
	Consignee_Address	NVARCHAR(127) NOT NULL,
	Consignee_Phone_Or_IdentityCard VARCHAR(30) NOT NULL,
	SendDate			DATETIME NOT NULL DEFAULT(GETDATE()),--Ngày gửi hàng
	Send_Status			BIT NOT NULL DEFAULT(0),
	Order_Max_Time		INT NOT NULL,--Thời gian gửi tối đa, tính từ ngày gửi
	Date_Create			DATETIME NOT NULL DEFAULT(GETDATE()),
	Description			NTEXT NULL
)

CREATE TABLE LG_Branches(
	Id					INT PRIMARY KEY IDENTITY(1,1),
	Location_Id			INT NOT NULL, --References to location_id
	Address				NVARCHAR(127) NOT NULL,
	Phone				VARCHAR(20) NULL,
	Fax					VARCHAR(20) NULL,
	Date_Installation	DATETIME NOT NULL DEFAULT(GETDATE())
)
CREATE TABLE LG_Orders_Branch(
	Order_Branch_Id		INT PRIMARY KEY IDENTITY(1,1),
	Order_Id			INT NOT NULL,
	Branch_Id			INT NOT NULL,
	Handover_Date		DATETIME NOT NULL DEFAULT(GETDATE())--Ngày bàn giao
)

CREATE TABLE LG_Bills
(
	Id				INT PRIMARY KEY IDENTITY(1,1),	
	Order_Branch_Id	INT NOT NULL,--References to  LG_Orders_Branch in the Orders table(trong LG_Orders_Branch da co Order_Id nen ko can fai referen sang bang Orders)
	Total_Cost		FLOAT NOT NULL,--Tổng chi phí, tính bằng VNĐ, Cơ số nghìn (1000)
	Payment_Status	BIT NOT NULL DEFAULT(0),
	Debt_Money		FLOAT NOT NULL DEFAULT(0),--Tiền nợ/Chưa trả hết
	DateCreate		DATETIME NOT NULL DEFAULT(GETDATE()),
	DatePayment		DATETIME NOT NULL DEFAULT(GETDATE()),
)

CREATE TABLE LG_Pricing-- Bảng cơ cấu tính giá
(
	Id				INT PRIMARY KEY IDENTITY(1,1),
	Title			NVARCHAR(127) NOT NULL,
	Numberic		INT NOT NULL DEFAULT(0),
	Description		NVARCHAR(300) NULL
)

ALTER TABLE LG_Employees ADD FOREIGN KEY (Branch_Id) REFERENCES LG_Branches(Id)

ALTER TABLE LG_Employee_Role ADD FOREIGN KEY (Emp_Id) REFERENCES LG_Employees(Id)
ALTER TABLE LG_Employee_Role ADD FOREIGN KEY (Role_Id) REFERENCES LG_Roles(Id)

ALTER TABLE LG_Role_Permissions ADD FOREIGN KEY (Role_Id) REFERENCES LG_Roles(Id)
ALTER TABLE LG_Role_Permissions ADD FOREIGN KEY (Per_Id) REFERENCES LG_Permissions(Id)

ALTER TABLE LG_Cargoes ADD FOREIGN KEY (Order_Id) REFERENCES LG_Orders(Id)

ALTER TABLE LG_Vehicle_Branch ADD FOREIGN KEY (Branch_Id) REFERENCES LG_Branches(Id)
ALTER TABLE LG_Vehicle_Branch ADD FOREIGN KEY (Vehicle_Id) REFERENCES LG_Vehicles(Id)

ALTER TABLE LG_Transportations ADD FOREIGN KEY (Branch_Id) REFERENCES LG_Branches(Id)
ALTER TABLE LG_Transportations ADD FOREIGN KEY(Route_Id) REFERENCES LG_Routes(Id)

ALTER TABLE LG_Transportation_Carego ADD FOREIGN KEY (Transp_Id) REFERENCES LG_Transportations(Id)
ALTER TABLE LG_Transportation_Carego ADD FOREIGN KEY (Carego_Id) REFERENCES LG_Cargoes(Id)
ALTER TABLE LG_Transportation_Vehicle ADD FOREIGN KEY (Transp_Id) REFERENCES LG_Transportations(Id)
ALTER TABLE LG_Transportation_Vehicle ADD FOREIGN KEY (Vehicle_Id) REFERENCES LG_Vehicles(Id)

ALTER TABLE LG_Route_Trip ADD FOREIGN KEY(Route_Id) REFERENCES LG_Routes(Id)
ALTER TABLE LG_Route_Trip ADD FOREIGN KEY(Trip_Id) REFERENCES LG_Trips(Id)

ALTER TABLE LG_Orders ADD FOREIGN KEY (Customer_Id) REFERENCES LG_Customers(Id)
ALTER TABLE LG_Orders ADD FOREIGN KEY (Send_Location_Id) REFERENCES LG_Locations(Id)
ALTER TABLE LG_Orders ADD FOREIGN KEY (Consignee_Location_Id) REFERENCES LG_Locations(Id)

ALTER TABLE LG_Branches ADD FOREIGN KEY (Location_Id) REFERENCES LG_Locations(Id)

ALTER TABLE LG_Orders_Branch ADD FOREIGN KEY (Order_Id) REFERENCES LG_Orders(Id)
ALTER TABLE LG_Orders_Branch ADD FOREIGN KEY (Branch_Id) REFERENCES LG_Branches(Id)

ALTER TABLE LG_Bills ADD FOREIGN KEY (Order_Branch_Id) REFERENCES LG_Orders_Branch(Order_Branch_Id)
GO
--Location Insert
INSERT INTO LG_Locations VALUES(N'Thành phố Hà Nội')
INSERT INTO LG_Locations VALUES(N'Thành phố Hồ Chí Minh')
INSERT INTO LG_Locations VALUES(N'Thành phố Hải Phòng')


-- Insert Base Branch
INSERT INTO LG_Branches(Location_Id, Address) VALUES(1, N'Khu 2,7 Bắc Thăng Long - Đông Anh - Hà Nội')
-- Insert Other Branches
INSERT INTO LG_Branches(Location_Id, Address) VALUES(2,N'Số 277 Trần Duy Hưng - Bình Thạnh - HCM')
INSERT INTO LG_Branches(Location_Id, Address) VALUES(3, N'Khu 2,7 Nguyễn Chí Thanh - Lạch Tray - Hải Phòng')
--Insert admin employee

INSERT INTO LG_Employees(
	Branch_Id,
	Username, Password,
	First_Name, Last_Name,
	Sex
) VALUES(
	1,
	'administrator','2a2fa4fe2fa737f129ef2d127b861b7e',--nhanvien
	'C0904I', 'FAT-2-FPT-Aptech',
	1
)
--Insert other employees
INSERT INTO LG_Employees(
	Branch_Id,
	Username, Password,
	First_Name, Last_Name,
	Sex
) VALUES(
	1,
	'hanoi_booking','2a2fa4fe2fa737f129ef2d127b861b7e',
	'A', 'Nguyen Van',
	0
)

INSERT INTO LG_Employees(
	Branch_Id,
	Username, Password,
	First_Name, Last_Name,
	Sex
) VALUES(
	1,
	'hanoi_godown','2a2fa4fe2fa737f129ef2d127b861b7e',
	'B', 'Nguyen Thi',
	0
)

INSERT INTO LG_Employees(
	Branch_Id,
	Username, Password,
	First_Name, Last_Name,
	Sex
) VALUES(
	1,
	'hanoi_delivery','2a2fa4fe2fa737f129ef2d127b861b7e',
	'C', 'Tran Van',
	1
)

--END HA NOI

INSERT INTO LG_Employees(
	Branch_Id,
	Username, Password,
	First_Name, Last_Name,
	Sex
) VALUES(
	1,
	'hcm_booking','2a2fa4fe2fa737f129ef2d127b861b7e',
	'A', 'Nguyen Van',
	0
)

INSERT INTO LG_Employees(
	Branch_Id,
	Username, Password,
	First_Name, Last_Name,
	Sex
) VALUES(
	1,
	'hcm_godown','2a2fa4fe2fa737f129ef2d127b861b7e',
	'B', 'Nguyen Thi',
	0
)

INSERT INTO LG_Employees(
	Branch_Id,
	Username, Password,
	First_Name, Last_Name,
	Sex
) VALUES(
	1,
	'hcm_delivery','2a2fa4fe2fa737f129ef2d127b861b7e',
	'C', 'Tran Van',
	1
)

-- END HCM

INSERT INTO LG_Employees(
	Branch_Id,
	Username, Password,
	First_Name, Last_Name,
	Sex
) VALUES(
	1,
	'haiphong_booking','2a2fa4fe2fa737f129ef2d127b861b7e',
	'A', 'Nguyen Van',
	0
)

INSERT INTO LG_Employees(
	Branch_Id,
	Username, Password,
	First_Name, Last_Name,
	Sex
) VALUES(
	1,
	'haiphong_godown','2a2fa4fe2fa737f129ef2d127b861b7e',
	'B', 'Nguyen Thi',
	0
)

INSERT INTO LG_Employees(
	Branch_Id,
	Username, Password,
	First_Name, Last_Name,
	Sex
) VALUES(
	1,
	'haiphong_delivery','2a2fa4fe2fa737f129ef2d127b861b7e',
	'C', 'Tran Van',
	1
)

-- Insert Roles

INSERT LG_Roles(Title) VALUES('Administrator')
INSERT LG_Roles(Title) VALUES('Booking Clerk')
INSERT LG_Roles(Title) VALUES('Godown Clerk')
INSERT LG_Roles(Title) VALUES('Delivery Clerk')

-- Insert Role_Employee
INSERT LG_Employee_Role(Emp_Id, Role_Id) VALUES(1,1)
INSERT LG_Employee_Role(Emp_Id, Role_Id) VALUES(2,2)
INSERT LG_Employee_Role(Emp_Id, Role_Id) VALUES(3,3)
INSERT LG_Employee_Role(Emp_Id, Role_Id) VALUES(4,4)
INSERT LG_Employee_Role(Emp_Id, Role_Id) VALUES(5,2)
INSERT LG_Employee_Role(Emp_Id, Role_Id) VALUES(6,3)
INSERT LG_Employee_Role(Emp_Id, Role_Id) VALUES(7,4)
INSERT LG_Employee_Role(Emp_Id, Role_Id) VALUES(8,2)
INSERT LG_Employee_Role(Emp_Id, Role_Id) VALUES(9,3)
INSERT LG_Employee_Role(Emp_Id, Role_Id) VALUES(10,4)


-- Customer Insert


INSERT INTO LG_Customers VALUES('012866030','Nguyen Dang Nam','FPT-Aptech','Dong Anh - Ha Noi','00084','Ha Noi','Viet Nam','0841656560229',NULL)
INSERT INTO LG_Customers VALUES('012353453','Kieu Van Thao','FPT-Aptech','Dong Anh - Ha Noi','00084','Vinh Phuc','Viet Nam','0841656560229',NULL)
INSERT INTO LG_Customers VALUES('012823422','Nguyen Sinh Huy','FPT-Aptech','Dong Anh - Ha Noi','00084','Ha Noi','Viet Nam','0841656560229',NULL)
INSERT INTO LG_Customers VALUES('0128634530','Bui Duc Thang','FPT-Aptech','Dong Anh - Ha Noi','00084','Some Where','Viet Nam','0841656560229',NULL)

-- Orders Insert
INSERT INTO LG_Orders(
	Customer_Id, 
	Send_Location_Id, 
	Consignee_Location_Id, 
	Send_Address, Consignee_Address, 
	Consignee_Phone_Or_IdentityCard, 
	Order_Max_Time) VALUES(
	1,
	1,2,--Send/Recieve Location
	'Khu 2,7 Bac Thang Long - Dong Anh - Ha Noi',--Address
	'So 28 Tran Khac Chan - Quy Hop - Nghe An',--Address
	'01656560229',--Identity
	2--Max time
	)

INSERT INTO LG_Orders(
	Customer_Id, 
	Send_Location_Id, 
	Consignee_Location_Id, 
	Send_Address, Consignee_Address, 
	Consignee_Phone_Or_IdentityCard, 
	Order_Max_Time) VALUES(
	2,
	2,2,--Send/Recieve Location
	'Khu 2,7 Bac Thang Long - Dong Anh - Ha Noi',--Address
	'So 28 Tran Khac Chan - Quy Hop - Nghe An',--Address
	'01656560229',--Identity
	2--Max time
	)

INSERT INTO LG_Orders(
	Customer_Id, 
	Send_Location_Id, 
	Consignee_Location_Id, 
	Send_Address, Consignee_Address, 
	Consignee_Phone_Or_IdentityCard, 
	Order_Max_Time) VALUES(
	3,
	1,3,--Send/Recieve Location
	'Khu 2,7 Bac Thang Long - Dong Anh - Ha Noi',--Address
	'So 28 Tran Khac Chan - Quy Hop - Nghe An',--Address
	'01656560229',--Identity
	2--Max time
	)

INSERT INTO LG_Orders(
	Customer_Id, 
	Send_Location_Id, 
	Consignee_Location_Id, 
	Send_Address, Consignee_Address, 
	Consignee_Phone_Or_IdentityCard, 
	Order_Max_Time) VALUES(
	4,
	3,2,--Send/Recieve Location
	'Khu 2,7 Bac Thang Long - Dong Anh - Ha Noi',--Address
	'So 28 Tran Khac Chan - Quy Hop - Nghe An',--Address
	'01656560229',--Identity
	2--Max time
	)
-- Insert 